// 已布置的作业列表
import express from 'express';
import pool from "../importantApi/db_connection.js";

let router = express.Router();
router.post("/", async (req, res) => {
    let {limit, page, userId, range} = req.body;
    if (!userId) {
        return res.status(401).json({
            code: 401,
            error: "缺少必要参数"
        })
    }
    limit = limit || 10;
    page = page || 1;
    range = range || 'all'
    let offset = (page - 1) * limit;
    // 根据userId查询身份和班级id
    let [result] = await pool.query(`
        select u.identity,
               json_arrayagg(c.class_id) as class_ids
        from userinfo u
                 inner join user_class c
                            on u.user_id = c.user_id
        where u.user_id = ${userId}
        group by u.user_id, u.identity
    `)
    if (result.length === 0 || result[0].class_ids.length === 0) {
        return res.status(201).json({
            code: 201,
            msg: "暂未加入班级"
        })
    }
    // 将班级id筛选出来
    let class_ids = ''
    result[0].class_ids.map(item => class_ids += !class_ids ? item : ',' + item)
    // 查询作业列表
    let sql = ''
    if (result[0].identity === 'teacher') { // 老师身份
        if (range === 'only') { // 获取指定身份的数据
            sql = `select h.homework_id,
                          h.teacher_id,
                          h.teacher_name,
                          h.class_id,
                          h.class_name,
                          h.work_type,
                          h.title,
                          h.subject,
                          h.content,
                          h.createtime,
                          h.deadline,
                          count(distinct d.student_id) as submitted_count,
                          (select count(*)
                           from userinfo
                           where class_id in (${class_ids})
                             and class_id in (select class_id from userinfo)
                             and identity = 'student') as total_count
                   from homework h
                            left join dowork d
                                      on h.homework_id = d.homework_id
                   where h.class_id in (${class_ids})
                     and h.teacher_id = ${userId}
                   group by h.homework_id, h.createtime
                   order by h.homework_id desc
                   limit ${offset}, ${limit}`
        } else { // 获取全部数据
            sql = `
                select h.homework_id,
                       h.teacher_id,
                       h.teacher_name,
                       h.class_id,
                       h.class_name,
                       h.work_type,
                       h.title,
                       h.subject,
                       h.content,
                       h.createtime,
                       h.deadline,
                       count(distinct d.student_id) as submitted_count,
                       (select count(*)
                        from userinfo
                        where class_id in (${class_ids})
                          and class_id in (select class_id from userinfo)
                          and identity = 'student') as total_count
                from homework h
                         left join dowork d
                                   on h.homework_id = d.homework_id
                where h.class_id in (${class_ids})
                group by h.homework_id, h.createtime
                order by h.homework_id desc
                limit ${offset}, ${limit}
            `
        }
    } else { // 学生身份，需要根据作业列表的id和用户id，匹配作业完成表的数据
        sql = `
            select h.homework_id,
                   h.teacher_name,
                   h.class_name,
                   h.work_type,
                   h.subject,
                   h.title,
                   h.content,
                   h.createtime,
                   h.deadline,
                   d.dowork_id,
                   case
                       when d.student_id is not null then 1
                       else 0
                       end as is_completed
            from homework h
                     left join dowork d
                               on h.homework_id = d.homework_id
                                   and d.student_id = ${userId}
            where h.class_id in (${class_ids})
            order by h.homework_id desc
            limit ${offset}, ${limit}
        `
    }
    try {
        let [result] = await pool.query(sql)
        res.status(200).json({
            code: 200,
            data: result
        })
    } catch (err) {
        console.log(err)
        return res.status(500).json({
            code: 500,
            error: err,
            msg: "查询错误：server error"
        })
    }
})
export default router;